﻿using AutoMapper;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Http;
using System.Net.Http.Headers;
using System.Linq;
using System.IO;
using System.Collections.Generic;
using housingsafety.server.Security;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Dapper;
using System.Text.Json;
using DocumentFormat.OpenXml.Office2010.Excel;
using NPOI.SS.Formula.Functions;
using StackExchange.Redis;
using System.Security.Policy;
using Quartz.Util;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 用户偏好设置(Ok)
    /// </summary>
    public class UserController : BaseController<UserController>
    {
        private const string AvatarGroupName = "avatars";
        private readonly string[] _pictureFormatArray = { ".png", ".jpg", ".jpeg", ".PNG", ".JPG", ".JPEG" };

        private readonly IRepository<ZmUser> _userRepository;
        private readonly ZmDbContext _dbContext;
        private readonly IMapper _mapper;
        private readonly IRepository<ZmSurveyUnit> _surveyUnitRepository;

        public UserController(IRepository<ZmUser> userRepository
            , IRepository<ZmSurveyUnit> surveyUnitRepository
            , ZmDbContext dbContext
            , IMapper mapper
            , ILogger<UserController> logger
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _mapper = mapper;
            _userRepository = userRepository;
            _dbContext = dbContext;
            _surveyUnitRepository = surveyUnitRepository;
        }

        /// <summary>
        /// 编辑个人信息
        /// </summary>
        /// <param name="userRegisterInput">个人信息</param>
        /// <returns>是否成功</returns>
        [HttpPost("preference/profile/edit")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        public RespRet Edit([FromBody] UserInfoInput userRegisterInput)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet(false, "no logined user");
            }

            var count = _userRepository.Count(u => string.Equals(u.Nickname, userRegisterInput.Nickname)
            && u.IsDelete != 1);
            if (count > 0)
            {
                return new RespRet(false, "nickname is repeat");
            }

            var firstOrDefault = _userRepository.FirstOrDefault(u => u.Number == userNumber);
            if (null != firstOrDefault)
            {
                firstOrDefault.Nickname = userRegisterInput.Nickname;
                firstOrDefault.Email = userRegisterInput.Email;
                firstOrDefault.ModifiedIp = this.IpAddress();
                firstOrDefault.ModifiedOn = DateTime.Now;
                _userRepository.Update(firstOrDefault);
                _userRepository.SaveChanges();

                return new RespRet(true, "Ok");
            }

            return new RespRet(false, "user not matched");
        }

        [HttpGet("preference/profile/info")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<UserDto>), 200)]
        public RespRet<UserDto> Profile()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<UserDto>(false, "no logined user");
            }

            var data = (from user in _dbContext.ZmUser
                        where user.Number == userNumber
                        select _mapper.Map<UserDto>(user)).FirstOrDefault();
            if (null != data)
            {
                if (data.JurisdictionCodes != null && data.JurisdictionCodes.Count > 0)
                {
                    data.JurisdictionNames = data.JurisdictionCodes.Select(_ => _.RegionName).ToList();
                }

                if (data.Role == PermissionRoleType.UnitAdmin || data.Role == PermissionRoleType.UnitInvestigator)
                {
                    var surveyUnit = _surveyUnitRepository
                        .FirstOrDefault(u => u.Id == data.SurveyUnitId && u.IsDelete != 1);
                    data.UnitName = surveyUnit?.Name;
                }

                return new RespRet<UserDto>(true, "Ok", data);
            }

            return new RespRet<UserDto>(false, "user not matched");
        }
        /// <summary>
        /// 获取自查端用户
        /// </summary>
        /// <param name="xzq"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="xzqdm"></param>
        /// <param name="towndm"></param>
        /// <param name="villagedm"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="qaakey"></param>
        /// <param name="fwbh"></param>
        /// <returns></returns>
        [HttpGet("preference/user/userlist")]
        public RespRet<Page<object>> userlist([FromQuery] string xzq, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord, [FromQuery] string qaakey, [FromQuery] string fwbh)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = " and role_type ='6' ";
            if (!string.IsNullOrEmpty(xzq))
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and xzq ='" + xzq + "'";
                }
                else if (user.RoleType == 3)
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
            }

            if (user.RoleType == 3)
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm)) 
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (nickname like '%" + txtKeyWord + "%' OR number like '%"+ txtKeyWord + "%' )";
            }
            if (!string.IsNullOrEmpty(qaakey))   //企安安ID
            {
                strWhere += " and (qaakey='" + qaakey + "')";
            }
            if (!string.IsNullOrEmpty(fwbh))  //房屋编号
            {
                strWhere += " and (number='" + fwbh + "')";
            }

            string sql = "select  ROW_NUMBER() OVER ()index,* from (select b1.*,to_char(b2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj  from ( select t1.role_type, t1.number,t1.nickname,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,t1.qaakey, t2.xzqdm,t2.xzq,t2.towndm,t2.town,t2.village,t2.villagedm,t2.address,disabled from zm_user t1 ,(select * from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings where fwzt is null )t2 where t1.number=t2.fwbh order by t1.create_on desc) b1 left join zm_pc_yh b2 on b1.number=b2.fwbh and b1.disabled!='1') t  where 1 = 1  and  disabled!='1'  " + strWhere + "   limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from (select b1.*,to_char(b2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj  from ( select t1.role_type, t1.number,t1.nickname,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,t1.qaakey, t2.xzqdm,t2.xzq,t2.towndm,t2.town,t2.village,t2.villagedm,t2.address,disabled from zm_user t1 ,(select * from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings where fwzt is null )t2 where t1.number=t2.fwbh order by t1.create_on desc) b1 left join zm_pc_yh b2 on b1.number=b2.fwbh and b1.disabled!='1') t  where 1 = 1  and  disabled!='1' " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 京办端用户
        /// </summary>
        /// <param name="xzq"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="xzqdm"></param>
        /// <param name="towndm"></param>
        /// <param name="villagedm"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="role_type"></param>
        /// <param name="qaakey"></param>
        /// <returns></returns>
        [HttpGet("preference/checkuser/checkuserlist")]
        public RespRet<Page<object>> checkuserlist([FromQuery] string xzq, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord, [FromQuery] string role_type, [FromQuery] string qaakey)
        {
            if(!txtKeyWord.IsNullOrWhiteSpace())
            {
                txtKeyWord = txtKeyWord.Trim();
            }
            
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (gxfw::text like '%" + model.RegionCode + "%')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (gxfw::text like '%" + model.RegionCode + "%')";
            }

            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and (gxfw::text like '%" + villagedm + "%')";
            }else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and (gxfw::text like '%" + towndm.Substring(0,9) + "%')";
            }else if  (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and (gxfw::text like '%" + xzqdm + "%')";
            }

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (qaakey::text like '%" + txtKeyWord+ "%'   or  nickname like '%" + txtKeyWord+"%' )";
            }
            if (!string.IsNullOrEmpty(role_type))
            {
                string where = "";
                string[] arrVal = role_type.Split(",");
                if (arrVal.Length > 0) 
                {
                    for (int i = 0; i < arrVal.Length; i++) 
                    {
                        if (!string.IsNullOrEmpty(arrVal[i]))
                        {
                            where += "  (role_type = '" + arrVal[i] + "') OR";
                        }
                    }

                    where = where.Substring(0,where.LastIndexOf("OR"));
                }
                  

                strWhere += " and ( " + where + ")";
            }
            string sql = "select ROW_NUMBER() OVER ()index,* from (select *,case when (village is null or village ='') and (town is null or town ='')   then CONCAT(xzq) else case when (village is null or village ='') and (town is not  null or town !='') then  CONCAT(xzq,'-' ,town)  else CONCAT(xzq,'-' ,town,'-',village)  end   end AS district  from \r\n(select number, nickname,\r\n(select region_name xzq from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select region_name town from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select region_name village from zm_department where id =district ->> 'RegionCode') end\r\n,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey,district gxfw from zm_user_cj t1 where disabled=0 ) t1  order by create_on desc,village asc) t     " +
                " where 1 = 1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from (select *,case when (village is null or village ='') and (town is null or town ='')   then CONCAT(xzq) else case when (village is null or village ='') and (town is not  null or town !='') then  CONCAT(xzq,'-' ,town)  else CONCAT(xzq,'-' ,town,'-',village)  end   end AS district  from \r\n(select number, nickname,\r\n(select region_name xzq from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select region_name town from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select region_name village from zm_department where id =district ->> 'RegionCode') end\r\n,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey,district gxfw from zm_user_cj t1 where disabled=0 ) t1  order by create_on desc,village asc) t  where 1 = 1     " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }

        [HttpGet("preference/checkuser/checkuserHouselist")]
        public RespRet<Page<object>> checkuserHouselist( [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string txtKeyWord,  [FromQuery] string qaakey, [FromQuery] string roletype)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (gxfw::text like '%" + model.RegionCode + "%')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (gxfw::text like '%" + model.RegionCode + "%')";
            }

            //if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            //{
            //    strWhere += " and (gxfw::text like '%" + villagedm + "%')";
            //}
            //else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            //{
            //    strWhere += " and (gxfw::text like '%" + towndm.Substring(0, 9) + "%')";
            //}
            //else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            //{
            //    strWhere += " and (gxfw::text like '%" + xzqdm + "%')";
            //}

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (nickname like '%" + txtKeyWord + "%' OR gxfw::text like '%" + txtKeyWord + "%')";
            }
            
            if (!string.IsNullOrEmpty(qaakey))
            {
                strWhere += " and (qaakey = '" + qaakey + "')";
            }
            string sql = "";
            if (roletype == "村级用户")
            {
                sql = "select ROW_NUMBER() OVER ()index,* from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t  " +
              " where 1 = 1  and disabled=0  " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            }
            else if(roletype=="乡镇用户")
            {
                sql = "select ROW_NUMBER() OVER ()index,* from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null and yhly='4' ) mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t  " +
              " where 1 = 1 and disabled=0  " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            }
            else if (roletype == "区级用户")
            {
                sql = "select ROW_NUMBER() OVER ()index,* from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null  and yhly='3' ) mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t  " +
              " where 1 = 1  and disabled=0 " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            }
            else if (roletype == "市级用户")
            {
                sql = "select ROW_NUMBER() OVER ()index,* from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null   and yhly='2') mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t  " +
              " where 1 = 1 and disabled=0  " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            }

            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            string sql_count = "";
            if (roletype == "村级用户")
            {
                sql_count = "select count(1) cnt from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t1.town,t1.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t where 1 = 1 and disabled=0   " + strWhere + " ";
            }
            else if (roletype == "乡镇用户")
            {
                sql_count = "select count(1) cnt from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t1.town,t1.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null  and yhly='4' ) mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t where 1 = 1  and disabled=0  " + strWhere + " ";
            }
            else if (roletype == "区级用户")
            {
                sql_count = "select count(1) cnt from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t1.town,t1.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null  and yhly='3' ) mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t where 1 = 1  and disabled=0  " + strWhere + " ";
            }
            else if (roletype == "市级用户")
            {
                sql_count = "select count(1) cnt from (select mm.*,nn.qaakey,nn.disabled from  (select t1.xzq,t1.town,t1.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null   and yhly='2') mm left join zm_user_cj nn on mm.create_by\r\n=nn.\"number\" ) t where 1 = 1  and disabled=0 " + strWhere + " ";
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 获取检查端用户的工作量
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="qaakey"></param>
        /// <returns></returns>
        [HttpGet("preference/checkuser/CheckUserWorkStatic")]
        public RespRet<Page<object>> CheckUserWorkStatic([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string txtKeyWord, [FromQuery] string role_type, [FromQuery] string cked, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (xzqdm ='"+ model.RegionCode+"')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (villagedm::text like '%" + model.RegionCode + "%')";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (nickname like '%" + txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(role_type))
            {
                strWhere += " and (role_type='" + role_type + "')";
            }
            else 
            {
                strWhere += " and (role_type in ('3','4','2'))";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            //string sql = "select ROW_NUMBER() OVER ()index,* from (select t.*,region_name xzq from (select xzqdm,SUM(CASE WHEN role_type='村级用户' THEN usercount ELSE 0 END) AS villageusercount,\r\n    SUM(CASE WHEN role_type='乡镇用户' THEN usercount ELSE 0 END) AS townusercount,\r\n    SUM(CASE WHEN role_type='区级用户' THEN usercount ELSE 0 END) AS xzqusercount,\r\n    SUM(CASE WHEN role_type='市级用户' THEN usercount ELSE 0 END) AS sjusercount,\r\n\t\tSUM(usercount) AS usercount\r\n\t\t from (select  xzqdm,role_type,count(1) usercount\r\nfrom (select number, nickname,(select  id xzqdm from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select id towndm from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select id villagedm from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1 ) t1   group by xzqdm,role_type) t   group by xzqdm) t left join zm_department m on t.xzqdm=m.id\r\n) t  " +
            //    " where 1 = 1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            string sql = "select ROW_NUMBER() OVER ()index,* from (select t1.*,coalesce(t2.jccount,0)jccount,t3.region_name xzq,t4.region_name town,t5.region_name village from ( "
           + "     select number, nickname, role_type, (select  id xzqdm from zm_department where id = (SUBSTRING(district->> 'RegionCode', 0, 7))), "
           + "     (case when role_type= '4' then(select id towndm from zm_department where id = concat(SUBSTRING(district->> 'RegionCode', 0, 10), '000'))  else case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end end) towndm,"
           + "      (case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end) villagedm, "
           + "      case when role_type= '5' then   '村级用户'  else case when role_type= '4' then '乡镇用户' else case when role_type= '3'  then '区级用户' else '市级用户' end end end role_type_name,"
           + "     to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1)t1 left join"
           + "     (select create_by, count(1) jccount, jcly from zm_pc_yh_cj where date(create_on) BETWEEN '"+startTime+"' and '"+endTime+"' group by create_by, jcly) t2 on t1.number = t2.create_by left join zm_department t3 on t1.xzqdm = t3.id"
           + "     left join zm_department t4 on t1.towndm = t4.id"
           + "     left join zm_department t5 on t1.villagedm = t5.id  order by jccount desc, t1.xzqdm, t1.villagedm) t where 1 = 1  " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            // string sql_count = "select count(1) cnt from (select t.*,region_name xzq from (select xzqdm,SUM(CASE WHEN role_type='村级用户' THEN usercount ELSE 0 END) AS villageusercount,\r\n    SUM(CASE WHEN role_type='乡镇用户' THEN usercount ELSE 0 END) AS townusercount,\r\n    SUM(CASE WHEN role_type='区级用户' THEN usercount ELSE 0 END) AS xzqusercount,\r\n    SUM(CASE WHEN role_type='市级用户' THEN usercount ELSE 0 END) AS sjusercount,\r\n\t\tSUM(usercount) AS usercount\r\n\t\t from (select  xzqdm,role_type,count(1) usercount\r\nfrom (select number, nickname,(select  id xzqdm from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select id towndm from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select id villagedm from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1 ) t1   group by xzqdm,role_type) t   group by xzqdm) t left join zm_department m on t.xzqdm=m.id\r\n) t where 1 = 1   " + strWhere + " ";
            string sql_count = "select count(1)cnt from (select t1.*,coalesce(t2.jccount,0)jccount,t3.region_name xzq,t4.region_name town,t5.region_name village from ( "
           + "     select number, nickname, role_type, (select  id xzqdm from zm_department where id = (SUBSTRING(district->> 'RegionCode', 0, 7))), "
           + "     (case when role_type= '4' then(select id towndm from zm_department where id = concat(SUBSTRING(district->> 'RegionCode', 0, 10), '000'))  else case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end end) towndm,"
           + "      (case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end) villagedm, "
           + "      case when role_type= '5' then   '村级用户'  else case when role_type= '4' then '乡镇用户' else case when role_type= '3'  then '区级用户' else '市级用户' end end end role_type_name,"
           + "     to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1)t1 left join"
           + "     (select create_by, count(1) jccount, jcly from zm_pc_yh_cj where date(create_on) BETWEEN '" + startTime + "' and '" + endTime + "' group by create_by, jcly) t2 on t1.number = t2.create_by left join zm_department t3 on t1.xzqdm = t3.id"
           + "     left join zm_department t4 on t1.towndm = t4.id"
           + "     left join zm_department t5 on t1.villagedm = t5.id  order by jccount desc, t1.xzqdm, t1.villagedm) t where 1 = 1" + strWhere;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 获取核查端用户的工作量
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="qaakey"></param>
        /// <returns></returns>
        [HttpGet("preference/hcuser/HcUserWorkStatic")]
        public RespRet<Page<object>> HcUserWorkStatic([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string txtKeyWord, [FromQuery] string role_type, [FromQuery] string cked, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (xzqdm ='" + model.RegionCode + "')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (villagedm::text like '%" + model.RegionCode + "%')";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (nickname like '%" + txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(role_type))
            {
                strWhere += " and (role_type='" + role_type + "')";
            }
            else
            {
                strWhere += " and (role_type in ('3','4','2'))";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            //string sql = "select ROW_NUMBER() OVER ()index,* from (select t.*,region_name xzq from (select xzqdm,SUM(CASE WHEN role_type='村级用户' THEN usercount ELSE 0 END) AS villageusercount,\r\n    SUM(CASE WHEN role_type='乡镇用户' THEN usercount ELSE 0 END) AS townusercount,\r\n    SUM(CASE WHEN role_type='区级用户' THEN usercount ELSE 0 END) AS xzqusercount,\r\n    SUM(CASE WHEN role_type='市级用户' THEN usercount ELSE 0 END) AS sjusercount,\r\n\t\tSUM(usercount) AS usercount\r\n\t\t from (select  xzqdm,role_type,count(1) usercount\r\nfrom (select number, nickname,(select  id xzqdm from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select id towndm from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select id villagedm from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1 ) t1   group by xzqdm,role_type) t   group by xzqdm) t left join zm_department m on t.xzqdm=m.id\r\n) t  " +
            //    " where 1 = 1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            string sql = "select ROW_NUMBER() OVER ()index,* from (select t1.*,coalesce(t2.hccount,0)hccount,t3.region_name xzq,t4.region_name town,t5.region_name village from ( "
           + "     select number, nickname, role_type, (select  id xzqdm from zm_department where id = (SUBSTRING(district->> 'RegionCode', 0, 7))), "
           + "     (case when role_type= '4' then(select id towndm from zm_department where id = concat(SUBSTRING(district->> 'RegionCode', 0, 10), '000'))  else case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end end) towndm,"
           + "      (case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end) villagedm, "
           + "      case when role_type= '5' then   '村级用户'  else case when role_type= '4' then '乡镇用户' else case when role_type= '3'  then '区级用户' else '市级用户' end end end role_type_name,"
           + "     to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1)t1 left join"
           + "     (select create_by, count(1) hccount, jcly from zm_pc_yh_sqjz where date(create_on) BETWEEN '" + startTime + "' and '" + endTime + "' group by create_by, jcly) t2 on t1.number = t2.create_by left join zm_department t3 on t1.xzqdm = t3.id"
           + "     left join zm_department t4 on t1.towndm = t4.id"
           + "     left join zm_department t5 on t1.villagedm = t5.id  order by hccount desc, t1.xzqdm, t1.villagedm) t where 1 = 1  " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            // string sql_count = "select count(1) cnt from (select t.*,region_name xzq from (select xzqdm,SUM(CASE WHEN role_type='村级用户' THEN usercount ELSE 0 END) AS villageusercount,\r\n    SUM(CASE WHEN role_type='乡镇用户' THEN usercount ELSE 0 END) AS townusercount,\r\n    SUM(CASE WHEN role_type='区级用户' THEN usercount ELSE 0 END) AS xzqusercount,\r\n    SUM(CASE WHEN role_type='市级用户' THEN usercount ELSE 0 END) AS sjusercount,\r\n\t\tSUM(usercount) AS usercount\r\n\t\t from (select  xzqdm,role_type,count(1) usercount\r\nfrom (select number, nickname,(select  id xzqdm from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select id towndm from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select id villagedm from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1 ) t1   group by xzqdm,role_type) t   group by xzqdm) t left join zm_department m on t.xzqdm=m.id\r\n) t where 1 = 1   " + strWhere + " ";
            string sql_count = "select count(1)cnt from (select t1.*,coalesce(t2.hccount,0)hccount,t3.region_name xzq,t4.region_name town,t5.region_name village from ( "
           + "     select number, nickname, role_type, (select  id xzqdm from zm_department where id = (SUBSTRING(district->> 'RegionCode', 0, 7))), "
           + "     (case when role_type= '4' then(select id towndm from zm_department where id = concat(SUBSTRING(district->> 'RegionCode', 0, 10), '000'))  else case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end end) towndm,"
           + "      (case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')  else '' end) villagedm, "
           + "      case when role_type= '5' then   '村级用户'  else case when role_type= '4' then '乡镇用户' else case when role_type= '3'  then '区级用户' else '市级用户' end end end role_type_name,"
           + "     to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1)t1 left join"
           + "     (select create_by, count(1) hccount, jcly from zm_pc_yh_sqjz where date(create_on) BETWEEN '" + startTime + "' and '" + endTime + "' group by create_by, jcly) t2 on t1.number = t2.create_by left join zm_department t3 on t1.xzqdm = t3.id"
           + "     left join zm_department t4 on t1.towndm = t4.id"
           + "     left join zm_department t5 on t1.villagedm = t5.id  order by hccount desc, t1.xzqdm, t1.villagedm) t where 1 = 1" + strWhere;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }

    }
}

